#!/usr/bin/env python
# encoding: utf-8



from flask import Blueprint, render_template
from app import db

index = Blueprint('index', __name__,
                 template_folder='templates',
                 static_folder='static')

@index.route('/')
def home():
    return 'Whelecome My Flask Example'




from app.common.libs.UserService import UserService
from app.common.libs.ElectronicService import ElectronicService
from app.common.libs.Helper import stringToDatetime, getCurrentDate
from app.model import *
import hashlib,time,random,decimal,json
import xlrd
import os

def readExcel(file, table):
    wb = xlrd.open_workbook(r'%s'%file)
    sheet_name = wb.sheet_names()[0]
    sheet = wb.sheet_by_name(sheet_name)

    data = []
    for i in range(0,sheet.nrows):
        rows = sheet.row_values(i)
        print(rows)
        if table =="user":
            user_model = User()
            #  print(int(float(rows[0])))
            if rows[0]:
                user_model.id = int(float(rows[0]))
            user_model.nickname = rows[1]
            if rows[3]:
                user_model.login_name = rows[3]
                user_model.mobile = rows[3]
                user_model.status = 1
                user_model.roles = '研发'
                user_model.avatar = 'https://wpimg.wallstcn.com/f778738c-e4f8-4870-b634-56703b4acafe.gif'
                user_model.login_salt = UserService.geneSalt()
                user_model.login_pwd = UserService.genePwd('123456', user_model.login_salt)
            user_model.updated_time = user_model.created_time = getCurrentDate()
            db.session.add(user_model)
        if table =="project":
            project_model = Project()
            if rows[0]:
                project_model.id = int(float(rows[0]))
            project_model.name = rows[1]
            if rows[2]:
                project_model.user_id = int(float(rows[2]))
            project_model.members = rows[3]
            project_model.updated_time = project_model.created_time = getCurrentDate()
            db.session.add(project_model)
        if table =="product":
            product_model = Product()
            if rows[0]:
                product_model.id = int(float(rows[0]))
            product_model.name = rows[1]
            if rows[3]:
               product_model.project_id = int(float(rows[3]))
            product_model.updated_time = product_model.created_time = getCurrentDate()
            db.session.add(product_model)
        if table =="component":
            supplier = rows[9]
            supplier_id = 0
            supplier_info = Supplier.query.filter_by( name = supplier  ).first()
            if not supplier_info:
                supplier_model = Supplier()
                supplier_model.name = supplier
                db.session.add(supplier_model)
                db.session.commit()
                supplier_id = supplier_model.id
            else:
                supplier_id = supplier_info.id

            ele_model = Electronic()
            if rows[0]:
                ele_model.id = int(float(rows[0]))
            ele_model.name = rows[1]
            ele_model.sn = ElectronicService.geneSn()
            ele_model.package = rows[2]
            try:
                ele_model.price = decimal.Decimal( float(rows[4] if rows[4] else 0) )
            except:
                pass
            ele_model.stock = int(rows[6] if rows[6] else 0)
            ele_model.position = rows[7]
            ele_model.status = 1
            ele_model.project_id = int(rows[11] if rows[11] else 0)

            #  print(supplier_id)
            ele_model.suppier_id  = int(supplier_id)

            ele_model.updated_time = ele_model.created_time = getCurrentDate()
            db.session.add(ele_model)
        if table =="component_in":
            elein_model = ElectronicInLog()
            #  print(rows)
            if rows[0]:
                elein_model.id = int(float(rows[0]))
            if rows[1]:
                elein_model.electronic_id = int(float(rows[1]))
            if rows[2]:
                elein_model.user_id = int(float(rows[2]))
            if rows[3]:
                elein_model.count = int(float(rows[3]))
            if rows[4]:
                elein_model.created_time = stringToDatetime(rows[4], format="%m/%d/%Y %H:%M:%S")
            if rows[5]:
                elein_model.note= rows[5]
            db.session.add(elein_model)
        if table =="component_out":
            eleout_model = ElectronicOutLog()
            if rows[0]:
                eleout_model.id = int(float(rows[0]))
            if rows[1]:
                eleout_model.electronic_id = int(float(rows[1]))
            if rows[2]:
                eleout_model.user_id = int(float(rows[2]))
            if rows[5]:
                eleout_model.count = int(float(rows[5]))
            if rows[6]:
                eleout_model.created_time = stringToDatetime(rows[6], format="%m/%d/%Y %H:%M:%S")
            if rows[8]:
                eleout_model.note= rows[8]
            db.session.add(eleout_model)

@index.route('/init')
def init():
    #  tables = ['user','component','component_in','component_out','project','product']
    tables = ['user']
    #  tables = ['product']
    #  tables = ['component']
    # tables = ['component_in']
    # tables = ['component_out']
    #  tables = ['project']
    try:
        for table in tables:
            readExcel('%s/docs/excel/%s.xlsx'%(os.getcwd(),table), table)
        db.session.commit()
        return '数据库初始化完成'
    except Exception as e:
        print(e)
        db.session.rollback()
        return '数据库初始化失败'
    return '数据库初始化'



